﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace KC.DataAccess.MV
{
	public class Utils
	{
		/// <summary>Gets the next unused friendly name per this title. Iterates ending number as needed.</summary>
		/// <param name="Title">Title to base friendly name on. Do not UrlEncode, I'll do that for you.</param>
		/// <returns>Next available friendly name with extension number as neccessary; UrlEncoded</returns>
		public static string GetNextAvailableFriendlyName(string Title)
		{
			string EncodedName = HttpUtility.UrlEncode(Title);
			using (MV8DataContext dc = new MV8DataContext())
			{
				int numTries = 1;
				while ((from p2 in dc.Posts where p2.FriendlyName == EncodedName select p2).Count() > 0)
				{
					EncodedName = HttpUtility.UrlEncode(Title) + numTries.ToString();
					numTries++;
				}
			}
			return EncodedName;
		}
		/// <summary>Updates all Tags on a given Post, saves changes, and returns the resulting Post.</summary>
		/// <param name="p">Post to update</param>
		/// <returns>Updated Post with new Tags</returns>
		public static Post UpdateTags(Post p)
		{
			MV8DataContext dc = new MV8DataContext();
			dc.TagToPosts.DeleteAllOnSubmit(p.TagToPosts);
			dc.SubmitChanges();
			string lowerbody = p.Body.ToLower(), lowertitle = p.Title.ToLower();
			foreach (Tag t in dc.Tags)
				if (lowerbody.Contains(t.Title.ToLower()) || lowertitle.Contains(t.Title.ToLower()))
				{
					TagToPost ttp = new TagToPost();
					ttp.PID = p.ID;
					ttp.TID = t.ID;
					dc.TagToPosts.InsertOnSubmit(ttp);
				}
			dc.SubmitChanges();
			return (from posts in dc.Posts where posts.ID == p.ID select posts).First();
		}
		/// <summary>Updates all Tags on a given Post, saves changes, and returns the resulting Post.</summary>
		/// <param name="p">Post to update</param>
		/// <param name="dc">DataContext</param>
		/// <returns>Updated Post with new Tags</returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1045:DoNotPassTypesByReference", MessageId = "1#")]
		public static Post UpdateTags(Post p, ref MV8DataContext dc)
		{
			if (dc == null)
				throw new ArgumentNullException("dc", "This throw exists just to satisfy CA1062.");

			dc.TagToPosts.DeleteAllOnSubmit(p.TagToPosts);
			dc.SubmitChanges();
			string lowerbody = p.Body.ToLower(), lowertitle = p.Title.ToLower();
			foreach (Tag t in dc.Tags)
				if (lowerbody.Contains(t.Title.ToLower()) || lowertitle.Contains(t.Title.ToLower()))
				{
					TagToPost ttp = new TagToPost();
					ttp.PID = p.ID;
					ttp.TID = t.ID;
					dc.TagToPosts.InsertOnSubmit(ttp);
				}
			dc.SubmitChanges();
			return (from posts in dc.Posts where posts.ID == p.ID select posts).First();
		}
		/// <summary>Updates Tags on all Posts.</summary>
		public static void UpdateAllTags()
		{
			MV8DataContext dc = new MV8DataContext();
			foreach (Post p in dc.Posts)
				UpdateTags(p, ref dc);
			dc.Dispose();
		}

		public static DataTable GetMonthlyHits()
		{
			return CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, "GetMonthlyHits", true);
		}
		public static DataTable GetPopularSearchQueries()
		{
			return CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, "GetPopularSearchQueries", true);
		}
		public static DataTable GetPostManagerPosts()
		{
			return CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, "GetPostManagerPosts", true);
		}
		public static void FullyDeletePost(int PostID, ref MV8DataContext dc)
		{
			// Unbind Activities
			foreach (Activity act in from a in dc.Activities where a.PID == PostID select a)
				act.PID = null;
			dc.SubmitChanges();

			// Delete records that the post is sitting on top of
			dc.CategoryToPosts.DeleteAllOnSubmit(from c in dc.CategoryToPosts where c.PID == PostID select c);
			dc.TagToPosts.DeleteAllOnSubmit(from t in dc.TagToPosts where t.PID == PostID select t);
			dc.SubmitChanges();

			// Delete the post
			dc.Posts.DeleteAllOnSubmit(from p in dc.Posts where p.ID == PostID select p);
			dc.SubmitChanges();
		}

		public static DataTable GetRecentReferrers()
		{
			string query =
				"select top 250 Referrer from Activities where \r\n" +
				" referrer is not null and \r\n" +
				" referrer <> \'\' and \r\n" +
				" referrer not like '%.google%' and \r\n" +
				" referrer not like '%.google%' and \r\n" +
				" referrer not like '%search%' and \r\n" +
				" referrer not like '%ask.com%' and \r\n" +
				" referrer not like '%flickr.%' and \r\n" +
				" referrer not like '%.bing.%' and \r\n" +
				" referrer not like '%localhost%' and ";
			foreach (string domain in KC.Constants.Domains.MyDomains)
				query += " referrer not like \'%" + domain + "%\' and \r\n";
			query +=
				" (select COUNT(id) from Blacklist where Referrer like '%' + domain + '%') = 0 and \r\n" +
				" (select COUNT(id) from BannedWords where Referrer like '%' + word + '%') = 0 \r\n" +
				"order by ID desc";

			DataTable dt = CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, query, false);
			foreach (DataRow dr in dt.Rows)
			{
				string url = HttpUtility.HtmlEncode(HttpUtility.UrlDecode(dr[0].ToString()));
				if (url.Contains("://"))
					url = url.Substring(url.IndexOf("://") + 3);
				if (url.Length > 60)
					url = url.Substring(0, 55) + "[...]";
				dr[0] = url;
			}

			for (int i = dt.Rows.Count - 1; i >= 0; i--)
				for (int j = 0; j < dt.Rows.Count; j++)
					if (i != j && dt.Rows[i][0].ToString().ToLower() == dt.Rows[j][0].ToString().ToLower())
					{
						dt.Rows.RemoveAt(i);
						break;
					}

			while (dt.Rows.Count > 25)
				dt.Rows.RemoveAt(24);

			return dt;
		}
		public static DataTable CallSql(string ConnectionString, string cmd, bool isSproc)
		{
			using (SqlConnection sc = new SqlConnection(ConnectionString))
			using (SqlCommand comm = new SqlCommand())
			{
				comm.Connection = sc;
				comm.CommandType = isSproc ? System.Data.CommandType.StoredProcedure : CommandType.Text;
				comm.CommandText = cmd;

				using (SqlDataAdapter da = new SqlDataAdapter(comm))
				using (DataTable dt = new DataTable())
				{
					try
					{
						da.Fill(dt);
						return dt;
					}
					catch (Exception ex) { throw new ApplicationException("SQL Server is Down", ex); }
				}
			}
		}

		public static List<int> SearchPosts(string[] words)
		{
			List<int> output = new List<int>();
			if (words.Length == 0) return output;

			string query = "select id from Posts where ";
			foreach (string part in words)
				query += "(title like '%" + part + "%' or body like '%" + part + "%') and ";
			if (query.EndsWith(" and ")) query = query.Substring(0, query.Length - 5);
			query += " order by created desc ";

			DataTable dt = CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, query, false);
			foreach (DataRow dr in dt.Rows)
				output.Add(int.Parse(dr[0].ToString()));
			return output;
		}
		public static List<int> SearchArticles(string[] words)
		{
			List<int> output = new List<int>();
			if (words.Length == 0) return output;

			string query = "select id from Articles where ";
			foreach (string part in words)
				query += "(title like '%" + part + "%' or body like '%" + part + "%') and ";
			if (query.EndsWith(" and ")) query = query.Substring(0, query.Length - 5);
			query += " order by created desc ";

			DataTable dt = CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, query, false);
			foreach (DataRow dr in dt.Rows)
				output.Add(int.Parse(dr[0].ToString()));
			return output;
		}
		public static List<int> SearchComments(string[] words)
		{
			List<int> output = new List<int>();
			if (words.Length == 0) return output;

			string query = "select id from Comments where ";
			foreach (string part in words)
				query += "body like '%" + part + "%' and ";
			if (query.EndsWith(" and ")) query = query.Substring(0, query.Length - 5);
			query += " order by [datetime] desc ";

			DataTable dt = CallSql(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, query, false);
			foreach (DataRow dr in dt.Rows)
				output.Add(int.Parse(dr[0].ToString()));
			return output;
		}
		/// <summary>Adds a generic History event to the MV6 database</summary>
		/// <param name="IPID">ID of IP object to tie the event to</param>
		/// <param name="LogEvent">Event string to log</param>
		/// <param name="Detail">Details of the string, if applicable</param>
		public static void AddHistory(int IPID, string LogEvent, string Detail = null)
		{
			using (MV8DataContext dc = new MV8DataContext())
			{
				History hist = new History();
				hist.IPID = IPID;
				hist.Event = LogEvent;
				hist.datetime = DateTime.Now;
				hist.Detail = Detail;
				dc.Histories.InsertOnSubmit(hist);
				dc.SubmitChanges();
			}
		}

		public static DataTable GetMonthlySessions()
		{
			return KC.DataAccess.Global.Global.CallSql_Sproc(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString, @"GetMonthlySessions");
		}
		public static IQueryable<Session> GetSessionsByTimes(DateTime StartDate, DateTime EndDate) {
			return from s in new MV8DataContext(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString).Sessions
				   where s.dStarted > StartDate && s.dEnded < EndDate
				   orderby s.ID descending
				   select s;
		}
		public static IQueryable<Activity> GetActivitiesByTimes(DateTime StartDate, DateTime EndDate) {
			return from a in new MV8DataContext(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString).Activities
				   where a.DateTime >= StartDate && a.DateTime <= EndDate
					&& a.Referrer != null && a.Referrer.Trim().Length > 0 && !a.Referrer.ToLower().Contains("kconnolly.net") && !a.Referrer.ToLower().Contains("damnednice.com") && !a.Referrer.ToLower().Contains("zi255.com") && !a.Referrer.ToLower().Contains("multimononline.com") && !a.Referrer.ToLower().Contains("manymon.com") && !a.Referrer.ToLower().Contains("http://localhost")  
				   orderby a.ID descending 
				   select a;
		}
		public static dynamic GetActivitiesBySession(int SessionId)
		{
			var result = from a in new MV8DataContext(ConfigurationManager.ConnectionStrings["MV8"].ConnectionString).Activities
						 where a.SID == SessionId
						 orderby a.SID descending, a.ID descending
						 select a;
			List<dynamic> result2 = new List<dynamic>();
			foreach (Activity act in result.AsEnumerable<Activity>()) {
				result2.Add(new {
					ID = act.ID,
					PID = act.PID,
					SID = act.SID,
					Request = act.Request,
					Referrer = act.Referrer
				});
			}
			return result2;
	   }
	}
}
